/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package Persistencia.Oracle;

import Persistencia.Entidades.Responsable;
import Persistencia.IResponsableDAO;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import oracle.jdbc.OraclePreparedStatement;
import oracle.jdbc.OracleTypes;

/**
 *
 * @author zeta
 */
public class ResponsableOracleDAO implements IResponsableDAO {
    private static ResponsableOracleDAO INSTANCE = null;

    /*
     * El contrustcor private no permite que se genere un constructor por defecto
    */
    private ResponsableOracleDAO() {

    }
    private synchronized static void createInstance() {
        if (INSTANCE == null) {
            INSTANCE = new ResponsableOracleDAO();
        }
    }

    public static ResponsableOracleDAO getInstance() {
        if (INSTANCE == null) createInstance();
        return INSTANCE;
    }
    public Integer insertResponsable(Responsable resp) {
       String sql ="INSERT INTO RESPONSABLE  (NOMBRE," +
               "APELLIDO,DNI,NOMBRE_USUARIO,CONTRASEÑA) " +
               "VALUES(?,?,?,?,?) RETURNING ID_RESPONSABLE INTO ?";
         try {
            OraclePreparedStatement pst = (OraclePreparedStatement) OracleDAOFactory.getConnection().prepareStatement(sql);
            pst.setString(1, resp.getNombre());
            pst.setString(2, resp.getApellido());
            pst.setInt(3, resp.getDni());
            pst.setString(4, resp.getNombreUsuario());
            pst.setString(5, resp.getContraseña());
            pst.registerReturnParameter(6, OracleTypes.NUMBER);
            pst.executeUpdate();

            ResultSet rs = pst.getReturnResultSet();
            if (rs.next()) {
                return rs.getInt(1);
            }
            return null;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return null;
        }
               
    }

    public boolean deleteResponsable(Responsable resp) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public boolean updateResponsable(Responsable resp) {
          String sql = "UPDATE RESPONSABLE SET NOMBRE = ?,"
                    + "APELLIDO = ?,DNI = ?,NOMBRE_USUARIO = ?,CONTRASEÑA=? "
                    + "WHERE ID_RESPONSABLE = ?";
            try {
            OraclePreparedStatement pst = (OraclePreparedStatement) OracleDAOFactory.getConnection().prepareStatement(sql);
            pst.setString(1, resp.getNombre());
            pst.setString(2, resp.getApellido());
            pst.setInt(3, resp.getDni());
            pst.setString(4, resp.getNombreUsuario());
            pst.setString(5, resp.getContraseña());
            pst.setInt(6, resp.getIdResponsable());

            return pst.execute();
        } catch (SQLException ex) {
            ex.printStackTrace();
            return false;
        }
    }

    public Responsable getResponsable(Integer idResponsable) {
        String sql = "SELECT * FROM RESPONSABLE  WHERE ID_RESPONSABLE = ?";
        try {
            OraclePreparedStatement pst = (OraclePreparedStatement) OracleDAOFactory.getConnection().prepareStatement(sql);
            pst.setInt(1, idResponsable.intValue());
            ResultSet rs = pst.executeQuery();
            if(rs.next()){
                Responsable resp = new Responsable();
                //resp.setIdTransporte(rs.getShort("ID_TRANSPORTE"));
                resp.setIdResponsable(rs.getShort("ID_RESPONSABLE"));
                resp.setApellido(rs.getString("APELLIDO"));
                resp.setNombre(rs.getString("NOMBRE"));
                resp.setContraseña(rs.getString("CONTRASEÑA"));
                resp.setNombreUsuario(rs.getString("NOMBRE_USUARIO"));
                resp.setDni(rs.getInt("DNI"));
                return resp;
            }else{
                return null;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
            return null;
        }
    }

    public Responsable getResponsable(String nombreApellido) {
        String sql = "SELECT * FROM RESPONSABLE  WHERE CONCAT(CONCAT(NOMBRE,' '),APELLIDO) LIKE '%?%' ";
        try {
            OraclePreparedStatement pst = (OraclePreparedStatement) OracleDAOFactory.getConnection().prepareStatement(sql);
            pst.setString(1, nombreApellido);
            ResultSet rs = pst.executeQuery();
            if(rs.next()){
                Responsable resp = new Responsable();

                resp.setIdResponsable(rs.getShort("ID_RESPONSABLE"));
                resp.setApellido(rs.getString("APELLIDO"));
                resp.setNombre(rs.getString("NOMBRE"));
                resp.setContraseña(rs.getString("CONTRASEÑA"));
                resp.setNombreUsuario(rs.getString("NOMBRE_USUARIO"));
                resp.setDni(rs.getInt("DNI"));
                return resp;
            }else{
                return null;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
            return null;
        }
    }

    public List<Responsable> getAllResponsables() {
       String sql = "SELECT * FROM RESPONSABLE";
        try {
            OraclePreparedStatement pst = (OraclePreparedStatement) OracleDAOFactory.getConnection().prepareStatement(sql);
               ResultSet rs = pst.executeQuery();
               List<Responsable> lResp = new ArrayList();
            while(rs.next()){
                Responsable resp = new Responsable();
                resp.setIdResponsable(rs.getShort("ID_RESPONSABLE"));
                resp.setApellido(rs.getString("APELLIDO"));
                resp.setNombre(rs.getString("NOMBRE"));
                resp.setContraseña(rs.getString("CONTRASEÑA"));
                resp.setNombreUsuario(rs.getString("NOMBRE_USUARIO"));
                resp.setDni(rs.getInt("DNI"));
                lResp.add(resp);

            }
               return lResp;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return null;
        }
    }

    public List<Responsable> selectResponsables(String condicion) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

}
